/**   
 * @{#} DbImporter.java Create on 2013-6-24 上午9:50:28   
 *   
 * Copyright (c) 2012 by Baidu.   
 */
package objtable.port;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.google.common.collect.Lists;
import com.mchange.v2.c3p0.ComboPooledDataSource;

import objtable.core.TableCenter;
import objtable.core.TableGenerator;
import objtable.exception.SqlRunerException;
import objtable.struct.DataType;
import objtable.struct.Row;

/**
 * @author <a href="mailto:zhangbo07@baidu.com">zhangbo07</a>
 * @version 1.0
 */

public class DbPort {
	private String driver = "com.mysql.jdbc.Driver";
	private String url = "jdbc:mysql://127.0.0.1:3306/test";
	private String name = "root";
	private String pwd = "root";
	private DataSource dataSource;
	private static Logger log;

	static {
		log = LoggerFactory.getLogger(DbPort.class);
	}

	public DbPort(String url, String name, String pwd) {
		this.url = url;
		this.name = name;
		this.pwd = pwd;
		dataSource = setupDataSource();
	}

	private DataSource setupDataSource() {
		final ComboPooledDataSource cpds = new ComboPooledDataSource();
		try {
			cpds.setDriverClass(driver);
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}
		cpds.setJdbcUrl(url);
		cpds.setUser(name);
		cpds.setPassword(pwd);
		cpds.setMinPoolSize(3);
		cpds.setMaxPoolSize(3);
		Runtime.getRuntime().addShutdownHook(new Thread(new Runnable() {
			public void run() {
				cpds.close();
			}
		}));
		return cpds;
	}

	public Object oneValueQuery(String sql) {
		Object result = null;
		Connection con = null;
		try {
			con = dataSource.getConnection();
			Statement st = con.createStatement();
			ResultSet set = st.executeQuery(sql);
			ResultSetMetaData rsmd = set.getMetaData();
			if (rsmd.getColumnCount() == 1 && set.next()) {
				result = set.getObject(1);
			}
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	// 获取数据，并放入全局表中
	public List<Row> fromDB(String objtablename, String sql) {
		TableGenerator tg = TableCenter.getTable(objtablename);
		List<Row> result = fromDB(tg, sql);
		TableCenter.putTableData(objtablename, result);
		return result;
	}

	// 单纯的获取数据
	public List<Row> fromDB(TableGenerator tg, String sql) {
		Connection con = null;
		ArrayList<Row> result = new ArrayList<Row>(100);
		try {
			con = dataSource.getConnection();
			Statement st = con.createStatement();
			log.info("sql:" + sql);
			ResultSet set = st.executeQuery(sql);
			ResultSetMetaData rsmd = set.getMetaData();
			List<FieldIdx> fis = getMatchFieldIdx(tg, rsmd);

			while (set.next()) {
				Row r = tg.newRow();
				for (FieldIdx fi : fis) {
					r.set(fi.name, getObject(fi, set));
				}
				result.add(r);
			}

			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	private final Object getObject(FieldIdx fi, ResultSet set)
			throws SQLException {
		switch (fi.dt) {
		case INT:
			return set.getInt(fi.idx);
		case DOUBLE:
			return set.getDouble(fi.idx);
		case LONG:
			return set.getLong(fi.idx);
		case STRING:
			return set.getString(fi.idx);
		default:
			return set.getObject(fi.idx);
		}
	}

	private List<FieldIdx> getMatchFieldIdx(TableGenerator tg,
			ResultSetMetaData rsmd) throws SQLException {
		List<FieldIdx> result = new ArrayList<DbPort.FieldIdx>();
		String[] fields = tg.getFields();
		ArrayList<String> al = Lists.newArrayList(fields);
		int count = rsmd.getColumnCount();
		for (int i = 0; i < count; i++) {
			int idx1 = i + 1;
			String label = rsmd.getColumnLabel(idx1);
			if (al.contains(label)) {
				FieldIdx a = new FieldIdx();
				a.name = label;
				a.idx = idx1;
				a.dt = tg.getFieldType(label);
				result.add(a);
			}
		}
		return result;
	}

	private class FieldIdx {
		String name;
		int idx;
		DataType dt;
	}

	public boolean runSql(List<String> sqls) {
		Connection con = null;
		try {
			con = dataSource.getConnection();
			con.setAutoCommit(false);// 更改JDBC事务的默认提交方式
			Statement st = con.createStatement();
			for (String sql : sqls) {
				st.addBatch(sql);
			}
			st.executeBatch();
			con.commit();// 提交JDBC事务
			con.setAutoCommit(true);// 恢复JDBC事务的默认提交方式
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SqlRunerException(e.getMessage());
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}
